﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleTesrt.LINQTest
{

    public class LinqJoin2
    {
        static void Main21(string[] args)
        {
            List<Student> student = new List<Student>(){
                new Student(){StuId="1",SName="张三",RoomID="1"},
                new Student(){StuId="2",SName="张1",RoomID="2"},
                new Student(){StuId="4",SName="张4",RoomID="1"},
                new Student(){StuId="3",SName="张2",RoomID="2"},
                new Student(){StuId="5",SName="张7",RoomID="1"},
                new Student(){StuId="9",SName="张8",RoomID="2"},
                new Student(){StuId="7",SName="张0",RoomID="1"},
                new Student(){StuId="8",SName="张9",RoomID="1"},
            };
            List<ClassRoom> Classroom = new List<ClassRoom>()
            {
            new ClassRoom(){RoomID="1",RoomName="甲班"},
            new ClassRoom(){RoomID="2",RoomName="乙班"}
            };
            List<Score> score = new List<Score>()
            {
                new Score(){ScoreID="1",ItemName="语文",StuId="3",Scores=78.3},
                new Score(){ScoreID="2",ItemName="数学",StuId="3",Scores=22.3},
                new Score(){ScoreID="3",ItemName="数学",StuId="2",Scores=54},
                new Score(){ScoreID="4",ItemName="英语",StuId="2",Scores=67.7},
                new Score(){ScoreID="5",ItemName="英语",StuId="5",Scores=24.5},
                new Score(){ScoreID="6",ItemName="物理",StuId="5",Scores=23.8},
                new Score(){ScoreID="7",ItemName="物理",StuId="2",Scores=53.8},
                new Score(){ScoreID="8",ItemName="数学",StuId="5",Scores=33.7},
            };

            //var query = student.Join(
            //    score,
            //    stu => stu.StuId,
            //    sc => sc.StuId,
            //    (s, sc) => new
            //    {
            //        学生姓名 = s.SName,
            //        科目 = sc.ItemName,
            //        得分 = sc.Scores
            //    }
            //    );
            var query = from a in student
                        join s in score
                        on a.StuId equals s.StuId
                        select new
                        {
                            学生姓名 = a.SName,
                            科目 = s.ItemName,
                            得分 = s.Scores
                        };
            foreach (var item in query)
            {
                Console.WriteLine(item.学生姓名 + "====" + item.科目 + "======" + item.得分);
            }
            //todo:算学生的总分
            Console.WriteLine("=======================算学生的总分===================");
            var queySum = from a in query
                          group a by a.学生姓名 into Ustudent
                          select new
                          {
                              学生姓名 = Ustudent.Key,
                              学生总分数 = Ustudent.Sum(a => a.得分)
                          };
            foreach (var item in queySum)
            {
                Console.WriteLine(item.学生姓名 + "-===============:" + item.学生总分数);
            }
            //todo:左查询(用join实现),DefaultIfEmpty后面括号中的参数是默认值，以students为主表，scores为副表，主表的信息全显示，副表只显示匹配学生Id相同的。没找到分数算0分
            var leftQuery = from s in student
                            join c in score
                            on s.StuId equals c.StuId
                            into sgroup
                            from sg in sgroup.DefaultIfEmpty(new Score() { StuId = s.StuId, ScoreID = "1", ItemName = "未上榜", Scores = 0 })
                            select new
                            {
                                学生科目 = sg.ItemName,
                                学生名称 = s.SName,
                                学生分数 = sg.Scores
                            };
            foreach (var item in leftQuery)
            {
                Console.WriteLine(item.学生名称 + "========" + item.学生科目 + "=======" + item.学生分数);
            }
            //todo:交叉查询
            Console.WriteLine("============交叉查询==================");
            var queryjiaocha = from a in student
                               from b in score
                               where a.StuId == b.StuId
                               select new
                               {
                                   学生科目 = b.ItemName,
                                   学生名称 = a.SName,
                                   学生分数 = b.Scores
                               };
            foreach (var item in queryjiaocha)
            {
                Console.WriteLine(item.学生名称 + "==========" + item.学生科目 + "=========" + item.学生分数);
            }
            //todo:类型转换操作！！！
            //todo:将数据源该成IEnumerable<T>类型使用使用AsEnumerable<T>方法，将数据改成IQueryable<T>类型使用AsQueryable<T>方法
            var queryIenum = from a in student.AsEnumerable<Student>()
                             select a;
            Console.WriteLine("转换成字典类型输出分数，取值【值类型】");
            //todo:将数据源改成Dictory<key,value>类型 测试无法取值
            //Dictionary<double,Score> dic = score.ToDictionary(a =>a.Scores);
            //for (int i = 0; i < dic.Count; i++)
            //{
            //    Console.WriteLine(dic[i].Scores); ;
            //}
            //todo:使用Enumerable类的Cast方法，该方法将非泛型的IEnumerable类型转换为泛型的IEnumerable<T>，至于转换成何种类型有Cast方法的TResult参数指定。
            ArrayList arrList = new ArrayList();
            for (int i = 0; i < 100; i++)
            {
                arrList.Add(i.ToString());
            }
            var query10 = from i in arrList.Cast<string>()
                          where i.IndexOf("0") > -1
                          select i;
            foreach (var item in query10)
            {
                Console.Write(item + ",");
            }
            //todo:使用 OfType<T> 筛选指定类型的元素
            ArrayList arrList2 = new ArrayList();
            arrList2.Add(1);
            arrList2.Add("茗洋");
            arrList2.Add("A");
            arrList2.Add(3);
            arrList2.Add("dd");
            var query11 = from item in arrList2.OfType<string>()
                          select item;
            foreach (var item in query11)
            {
                Console.WriteLine(item + ",");
            }
            //todo:转换为一对多的字典Lookup<TKey,TSource>，与Dictionary区别就是 相同key会对应多个值
            var querylookup = from a in student
                              join c in Classroom
                              on a.RoomID equals c.RoomID
                              select new Student
                              {
                                  RoomID = a.RoomID,
                                  StuId = a.StuId,
                                  SName = a.SName,
                                  RoomName = new ClassRoom { RoomID = c.RoomID, RoomName = c.RoomName }
                              };
            ILookup<string, Student> queryloup = querylookup.ToLookup(f => f.RoomName.RoomName);
            foreach (var item in queryloup)
            {
                Console.WriteLine("key值为" + item.Key + "的班级");
                foreach (var items in item)
                {
                    Console.WriteLine(items.RoomName.RoomName + "========" + items.SName + "========" + items.RoomID);
                }
            }
            //todo:使用字符串的 EndsWith或者StartsWith过滤，使用CompareTo比较字符串的大小（）
            var query14 = from o in student
                          where o.SName.StartsWith("张")
                          select o;
            foreach (var item in query14)
            {
                Console.WriteLine(item.SName + ",");
            }
            //todo:操作泛型作排序列表SortList<TKey,TValue> SortList<TKey,TValue>表示按照键进行排序的键/值对的集合，键/值对是KeyValuePair<TKey,TValue>
            SortedList<int, Student> users = new SortedList<int, Student>
                 {
                            {2,new Student{StuId="2",SName="钱一",RoomID="1"}},
                            {12,new Student{StuId="12",SName="赵二",RoomID="1"}},
                            {1,new Student{StuId="1",SName="张三",RoomID="2"}},
                 };
            Console.WriteLine("未按学生姓名排序前的结果如下：");
            foreach (var item in users)
            {
                Console.Write("     键" + item.Key + "：" + item.Value.SName);
            }
            var query15 = from s in users
                          orderby s.Value.SName descending
                          select s;
            Console.WriteLine();
            Console.WriteLine("按学生姓名排序后的结果如下：");
            foreach (var item in query15)
            {
                Console.Write("     键" + item.Key + "：" + item.Value.SName);
            }
            //todo:操作泛型双向链表LinkedList<T>泛型双向列表LinkedList<T>表示由T指定类型的双向链表，它通过当前元素可以直接访问该元素的前一个或者后一个元素（如果不存在返回空），元素为LinkedListNode<T>类型
            LinkedList<int> ints2 = new LinkedList<int>();
            ints2.AddFirst(0);
            for (int i = 1; i < 10; i++)
            {
                ints2.AddAfter(ints2.Find(i - 1), i);
            }
            Console.WriteLine("使用Linq过滤，排序泛型双向链表");
            var query16 = from item in ints2
                          where item > 0 && item < 9
                          orderby item descending
                          select item;
            //另一种方式
            var querylinkedlist = ints2.Where(x => x > 0 && x < 9).OrderByDescending(x => x);
            //显示结果
            foreach (var item in querylinkedlist)
            {
                Console.Write(item.ToString() + ",");
            }
            //todo:操作泛型队列Queue<T>
            //先进先出的线性表，使用Enqueue方法进行元素入队（添加）操作，使用Dequeue方法进行元素出队（删除）操作；
            //使用Clear方法进行清空队列操作。Queue类没有实现IEnumerable<T>接口或IEnumerable接口，
            //所以不能使用LINQ直接操作Queue类型的对象，可以使用Queue对象的Cast方法先把它转换为IEnumerable<T>类型，然后在使用LINQ对齐操作
            Queue<Student> queues = new Queue<Student>();
            queues.Enqueue(new Student() { StuId = "1", SName = "章黑", RoomID = "2" });
            queues.Enqueue(new Student() { StuId = "2", SName = "黑工", RoomID = "1" });
            queues.Enqueue(new Student() { StuId = "3", SName = "紫衣", RoomID = "1" });
            queues.Enqueue(new Student() { StuId = "4", SName = "张锋", RoomID = "2" });
            queues.Enqueue(new Student() { StuId = "5", SName = "库萨克", RoomID = "1" });
            var query18 = queues.OrderByDescending(x => x.StuId);
            //显示结果
            foreach (var item in query18)
            {
                Console.WriteLine(item.SName + ",");
            }
            //todo: 操作泛型堆栈Stack<T>
            //后进先出的线性表，使用Push方法进行元素入栈（添加）操作，使用Dequeue方法进行元素出栈（删除）操作；
            //使用Clear方法进行清空堆栈操作。Stack类没有实现IEnumerable<T>接口或IEnumerable接口，
            //所以不能使用LINQ直接操作Queue类型的对象，可以使用Stack对象的Cast方法先把它转换为IEnumerable<T>类型，然后在使用LINQ对齐操作
            Stack<Student> stuckst = new Stack<Student>();
            stuckst.Push(new Student() { StuId = "1", SName = "章黑", RoomID = "1" });
            stuckst.Push(new Student() { StuId = "3", SName = "看得开", RoomID = "2" });
            stuckst.Push(new Student() { StuId = "2", SName = "结果", RoomID = "2" });
            stuckst.Push(new Student() { StuId = "4", SName = "看似", RoomID = "1" });
            stuckst.Push(new Student() { StuId = "5", SName = "看visa", RoomID = "1" });
            stuckst.Push(new Student() { StuId = "6", SName = "oil", RoomID = "2" });
            var querysd = from a in stuckst
                          where a.StuId != "1" && a.RoomID == "2"
                          select a;
            foreach (var item in querysd)
            {
                Console.WriteLine(item.StuId + "===" + item.SName + "====" + item.RoomID);
            }
            //todo:操作泛型哈希集HashSet<T>
            //后泛型哈希集HashSet<T>是由T指定类型的基于集合的模型，泛型哈希集可以提供高性能的如并集，
            //交集，补集等集合运算。泛型哈希集中的元素不能重复。泛型哈希集提供许多集合操作，如并集，交集，补集等。
            HashSet<Student> hashst = new HashSet<Student>() 
            {
                new Student() { StuId = "1", SName = "章黑", RoomID = "1" } ,
                new Student() { StuId = "3", SName = "开发", RoomID = "1" } ,
                new Student() { StuId = "5", SName = "斯达康", RoomID = "1" } ,
                new Student() { StuId = "6", SName = "ivcke", RoomID = "1" } ,
                new Student() { StuId = "7", SName = "看得开", RoomID = "1" } ,
                new Student() { StuId = "9", SName = "看似", RoomID = "1" } 
            };
            var queryhash = from a in hashst
                            orderby a.StuId ascending
                            select a;
            foreach (var item in queryhash)
            {
                Console.WriteLine(item.SName+"========"+item.RoomID);
            }
            //todo:操作泛型字典Dictionary<TKey,TValue>键不能重复，值可重复，键不会自动排序
            var query21 = from o in student.ToDictionary(a=>a.SName)
                          where o.Value.SName.CompareTo("张") > 0 //学生姓名大于“张”
                          orderby o.Key
                          select o;
            foreach (var item in query21)
            {
                Console.WriteLine(item.Key + ":" + item.Value.SName);
            }
            //todo:操作泛型排序字典SortedDictionary<TKey,TValue>
            //与Dictionary<TKey,TValue>的区别：泛型排序字典对添加的元素自动按键进行排序,代码例如：
            SortedDictionary<int, Student> sd = new SortedDictionary<int, Student> { };
            //todo:操作泛型通用集合Collection<T>,跟List<T>一样用
            //todo:操作泛型绑定列表 BindingList<T>
            //泛型绑定列表BindingList<T>能够提供支持数据绑定的泛型集合，他提供了IBindingList接口的具体泛型实现，
            //可以用作创建双向数据绑定机制的积累，还可以通过AddNew方法支持创建实例
            //跟List<T>一样用就行了
        }
    }
    public class Score
    {
        public string ScoreID { get; set; }
        // 学科名称
        public string ItemName { get; set; }
        // 学生ID
        public string StuId { get; set; }
        // 学生
        public Student Students { get; set; }
        // 分数
        public double Scores { get; set; }
    }
    public class ClassRoom
    {
        public string RoomID { get; set; }
        public string RoomName { get; set; }
    }
    public class Student
    {
        public string StuId { get; set; }
        public string SName { get; set; }
        public string RoomID { get; set; }
        public ClassRoom RoomName { get; set; }
    }

}
